package com.unis.mapper.znzf;

import com.unis.model.znzf.CbGl;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

@Repository
public interface CbGlMapper extends Mapper<CbGl> {
    //@Select("SELECT JJDPK,LRDWMC,WMSYS.WM_CONCAT(CARDNUMBER) CBYJ FROM TB_ZNZF_CB_GL A WHERE JJDPK<>#{jjdpk} AND EXISTS (SELECT 1 FROM TB_ZNZF_CB_GL B WHERE A.CBBH=B.CBBH AND JJDPK=#{jjdpk} AND YXX='1') GROUP BY JJDPK,LRDWMC ")
    //@Select("SELECT JJDPK,JJDHM,AFSJ,BARXM,SLDWMC,CBYJ FROM TB_FZZX_JJD C,(SELECT JJDPK,WMSYS.WM_CONCAT(CARDNUMBER) CBYJ FROM TB_ZNZF_CB_GL A WHERE JJDPK<>#{jjdpk} AND EXISTS (SELECT 1 FROM TB_ZNZF_CB_GL B WHERE A.CBBH=B.CBBH AND JJDPK=#{jjdpk} AND YXX='1') GROUP BY JJDPK) D WHERE C.PK=D.JJDPK ")
    @Select("select  jjdpk,jjdhm,afsj,barxm,sldwmc,cbyj from (select jjdpk,jjdhm,afsj,barxm,sldwmc,wmsys.wm_concat(cardnumber) over(partition by a.jjdpk) cbyj, row_number() over(partition by a.jjdpk order by A.rksj) rank from tb_znzf_cb_gl a ,(select distinct cbbh from tb_znzf_cb_gl where jjdpk=#{jjdpk} and yxx='1') b where a.cbbh=b.cbbh and a.jjdpk<>#{jjdpk} ) c where c.rank=1 order by c.afsj desc")
    List<Map> selectByJjdpk(@Param("jjdpk") String jjdpk);
}